#!/usr/bin/env python
"""
# -*- coding: utf-8 -*-
# @Time    : 2020/10/20 16:47
# @Author  : AshJo
# @Desc    : 
# @Filename: sql_demo.py
# @Version : v1.0
"""


# 查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
# select * from score where student_id=1;
# select course_id from score where student_id=1;
# select distinct student_id,sname from student as t1 inner join score as t2
# on t1.sid=t2.student_id where course_id in (select course_id from score where student_id=1);

# 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
# select student_id as sid2,num as n2 from score where course_id=2;
# select student_id as sid1,num as n1 from score where course_id=1;
#
# select * from (select student_id as sid2,num as n2 from score where course_id=2) as t2
# inner join
# (select student_id as sid1,num as n1 from score where course_id=1) as t1
# on t2.sid2=t1.sid1 where n2<n1;
#
#
# select sid,sname from student right join
# (select * from (select student_id as sid2,num as n2 from score where course_id=2) as t2
# inner join
# (select student_id as sid1,num as n1 from score where course_id=1) as t1
# on t2.sid2=t1.sid1 where n2<n1) as tmp on tmp.sid1=student.sid;

# 查询“生物”课程比“物理”课程成绩高的所有学生的学号；
# select cid from course where cname='生物';
# select cid from course where cname='物理';
#
# select student_id as sid1,num as n1 from score where course_id=(select cid from course where cname='生物');
# select student_id as sid2,num as n2 from score where course_id=(select cid from course where cname='物理');
#
#
# select * from (select student_id as sid1,num as n1 from score where course_id=
# (select cid from course where cname='生物')) as t1
# inner join
# (select student_id as sid2,num as n2 from score where course_id=
# (select cid from course where cname='物理')) as t2
# on t1.sid1=t2.sid2 where n1>n2;
#
# select sid1 from
# (select student_id as sid1,num as n1 from score where course_id=
# (select cid from course where cname='生物')) as t1
# inner join
# (select student_id as sid2,num as n2 from score where course_id=
# (select cid from course where cname='物理')) as t2
# on t1.sid1=t2.sid2 where n1>n2;


# 13、查询没学过“张磊老师”课的同学的学号、姓名；
# select tid from teacher where tname='张磊老师';
# select cid from course where teacher_id=(select tid from teacher where tname='张磊老师');
#
# select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='张磊老师'))
#
#
# select sid,sname from student where sid not in
# (select distinct student_id from score where course_id in
# (select cid from course where teacher_id=(select tid from teacher where tname='张磊老师')));

# 15、查询学过“李平老师”所教的所有课的同学的学号、姓名；
# select cid from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师';
# select count(cid) from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师';
#
#
# select * from score where course_id in
# (select cid from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师')
# group by student_id having count(*)=
# (select count(cid) from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师')
#
#
# select sid,sname from student right join (
# select student_id from score where course_id in
# (select cid from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师')
# group by student_id having count(*)=
# (select count(cid) from course inner join teacher on course.teacher_id=teacher.tid where tname='李平老师')
# ) as tmp on tmp.student_id=student.sid